/*******************************************************************************
 * Copyright (c) 2010 liw.
 * All rights reserved. 
 * 
 * This file is part of VanBus.
 * 
 *     VanBus is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     VanBus is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 * 
 *     You should have received a copy of the GNU General Public License
 *     along with VanBus.  If not, see <http://www.gnu.org/licenses/>.
 * Contributors:
 *     liw - initial API and implementation
 ******************************************************************************/
package org.niclab.vanbus.database;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.niclab.vanbus.model.BusRoute;
import org.niclab.vanbus.model.BusStop;
import org.niclab.vanbus.model.Reminder;
import org.niclab.vanbus.model.StartStopReminder;
import org.niclab.vanbus.model.Reminder.ReminderType;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;

public class ReminderDAO extends AbstractDAO{

	
	public static final String DATABASE_TABLE_NAME = ApplicationDataBase.DATABASE_TABLE_REMINDER;
	
	public static final String[] TABLE_REMINDER_COLUMNS={"_id", "REMINDER_ID","TYPE","DESC", "ALARM_TIME", "STOP_DB_ID","ROUTE_DB_ID"};
	public static final String[] TABLE_REMINDER_COLUMNS_PROPERTIES={"INTEGER PRIMARY KEY ASC AUTOINCREMENT,",
		"VARCHAR(255) UNIQUE NOT NULL,",
		"CHARACTER(1) NOT NULL,",
		"VARCHAR(255),",
		"DATATIME,",
		"INTEGER REFERENCES STOP (_id),",
		"INTEGER REFERENCES ROUTE (_id)"};
	
	public static final String TABLE_REMINDER_CONSTRAINTS="";
	
	
	public ReminderDAO(SQLiteOpenHelper openHelper){

		super(openHelper);
	}
	
	//if reminderDBID <=0
	//it returns cursor to all reminders
	//if type == null
	// it returns all reminders
	public Cursor getReminders(int reminderDBID,Reminder.ReminderType type){
			
		Cursor cursor = null;
		
		SQLiteQueryBuilder query = new SQLiteQueryBuilder();
		String tables = ReminderDAO.DATABASE_TABLE_NAME + " INNER JOIN " + BusStopDAO.DATABASE_TABLE_NAME + " ON reminder.STOP_DB_ID = stop._id"
								+" INNER JOIN "+BusRouteDAO.DATABASE_TABLE_NAME+" ON reminder.ROUTE_DB_ID=route._id";
		query.setTables(tables);
		
		Map<String, String> columns = new HashMap<String,String>();
		columns.put("REMINDER_DB_ID","reminder._id");
		columns.put("REMINDER_ID", "reminder.REMINDER_ID as REMINDER_ID");
		columns.put("TYPE", "reminder.TYPE ");
		columns.put("DESC", "reminder.DESC ");
		columns.put("ALARM_TIME", "reminder.ALARM_TIME");
		columns.put("STOP_DB_ID", "stop._id as STOP_DB_ID");
		columns.put("STOP_ID", "stop.STOP_ID");
		columns.put("STOP_NAME", "stop.NAME as STOP_NAME");
		columns.put("ROUTE_ID", "route.ROUTE_ID as ROUTE_ID");
		columns.put("ROUTE_NAME", "route.NAME as ROUTE_NAME");
		columns.put("ROUTE_DIR", "route.DIR as ROUTE_DIR");

		
		query.setProjectionMap(columns);
		String[] projectionIn ={"REMINDER_DB_ID","REMINDER_ID","TYPE","DESC","ALARM_TIME","STOP_ID","STOP_NAME","ROUTE_ID","ROUTE_NAME","ROUTE_DIR"};
		
		StringBuilder where = new StringBuilder();
		where.append("(1=1)");
		List<String> args = new ArrayList<String>();
		
		if(reminderDBID>0){
			where.append(" AND (reminder._id = ?)");
			args.add(String.valueOf(reminderDBID));
		}
		
		if(type != null){
			where.append(" AND (TYPE = ?)");
			args.add(type.getTypeStr());
			
		}
		
		String[] argsArray = new String[args.size()];
		args.toArray(argsArray);
		cursor = query.query(getDB(), projectionIn, where.toString(), argsArray, null, null, null);
		
		return cursor;
		
	}
	
	
	
	/*public Cursor getReminder(String id){
		
	
		Cursor cursor = null;
		
		SQLiteQueryBuilder query = new SQLiteQueryBuilder();
		String tables = ReminderDAO.DATABASE_TABLE_NAME + "AS reminder INNER JOIN " + BusStopDAO.DATABASE_TABLE_NAME + "AS stop"+"ON (reminder.STOP_ID = stop._id)";
		query.setTables("tables");
		
		Map<String, String> columns = new HashMap<String,String>();
		columns.put("REMINDER_DB_ID","reminder._id");
		columns.put("REMINDER_ID", "REMINDER_ID");
		columns.put("TYPE", "TYPE");
		columns.put("DESC", "DESC");
		columns.put("STOP_DB_ID", "stop._id");
		columns.put("STOP_ID", "STOP_ID");
		columns.put("STOP_NAME", "stop.NAME");
		
		query.setProjectionMap(columns);
		String[] projectionIn ={"reminder._id","REMINDER_ID","TYPE","DESC","stop._id","STOP_ID","stop.NAME"};
		
		String[] args = new String[1];
		args[0] = id;
		
		cursor = query.query(getDB(), projectionIn, "REMINDER_ID=?", args, null, null, null);
		return cursor;
		
	}*/

	
	public void deleteReminder(int reminderDBID){

			
		String[] args = new String[1];
		args[0] = ""+reminderDBID;
		getDB().delete(DATABASE_TABLE_NAME, "_id=?", args);

		
	}
	
	public void deleteReminder(String id){

		String[] args = new String[1];
		args[0] = id;
		getDB().delete(DATABASE_TABLE_NAME, "REMINDER_ID=?", args);

		
	}
	
	public int insertReminder(Reminder reminder){

		long rowID =-1;

		
		getDB().beginTransaction();
		
		try{

			BusStop stop = reminder.getStop();
			
			BusStopDAO busStopDao = new BusStopDAO(getOpenHelper());
			
			int stopDbId = busStopDao.getStopDbId(stop);
			
			if(stopDbId <= 0){
				//insert stop record
				
				stopDbId=busStopDao.insertBusStop(stop);
				
				
			}
			
			BusRoute route = reminder.getRoute();
			BusRouteDAO routeDao = new BusRouteDAO(getOpenHelper());
			int routeDbId = routeDao.getRouteDbId(route);
			if(routeDbId <= 0){
				routeDbId = routeDao.insertBusRoute(route);
			}
			
			
			ContentValues values = new ContentValues();
			//"ID", "TYPE", "STOP_ID"
			values.put("REMINDER_ID", reminder.getReminderId());
			values.put("TYPE", reminder.getType().getTypeStr());
			values.put("DESC", reminder.getDescription());
			if(reminder.getType() == Reminder.ReminderType.StartStopReminder){
				values.put("ALARM_TIME", ((StartStopReminder)reminder).getAlarmTimeAsString());
			}
			values.put("STOP_DB_ID",stopDbId);
			values.put("ROUTE_DB_ID", routeDbId);
			rowID = getDB().insertOrThrow(DATABASE_TABLE_NAME, "REMINDER_ID", values );

			getDB().setTransactionSuccessful();
			
		}
		finally{
			getDB().endTransaction();
		}

		
		return (int) rowID;
		
		
	}
}
